package by.bsu.touragency.db.dao;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.GregorianCalendar;
import org.apache.log4j.Logger;
import by.bsu.touragency.db.beans.City;
import by.bsu.touragency.db.beans.Transport;
import by.bsu.touragency.db.beans.TransportType;
import by.bsu.touragency.db.connection.DBConnector;

public class TransportDAO extends AbstractDAO {

	public static final String SQL_TRANSPORT_CREATE = " INSERT INTO tourAgencyDB.transport (dateFrom, dateTo, viewId, cityFromId, cityToId) VALUES (?, ?, ?, ?, ?);";
	public static final String SQL_TRANSPORT_CREATE_GET_ID = "SELECT * FROM tourAgencyDB.transport WHERE dateTo = ? AND dateFrom = ? AND cityToId = ? AND cityFromId = ? AND viewId = ?;";
	public static final String SQL_TRANSPORT_IS_EXIST = " SELECT COUNT(*) AS rowcount FROM tourAgencyDB.transport WHERE dateTo = ? AND dateFrom = ? AND cityToId = ? AND cityFromId = ? AND viewId = ?;";
	public static final String SQL_TRANSPORT_READ_BY_ID = "SELECT * FROM tourAgencyDB.transport WHERE (id= ?);";
	public static final String SQL_TRANSPORT_IS_USED = " SELECT COUNT(*) AS rowcount FROM tourAgencyDB.tour WHERE transportId = ? ;";
	public static final String SQL_TRANSPORT_UPDATE_DATETO = "UPDATE tourAgencyDB.transport  SET dateTo = ? WHERE (id = ?);";
	public static final String SQL_TRANSPORT_UPDATE_DATEFROM = "UPDATE tourAgencyDB.transport  SET dateFrom = ? WHERE (id = ?);";
	public static final String SQL_TRANSPORT_DELETE = "DELETE FROM tourAgencyDB.transport WHERE id = ?";
	private static final String SQL_TRANSPORT_READ_BY_ByDatesViewAndCity = "SELECT * FROM tourAgencyDB.transport WHERE (dateTo= ?) AND (dateFrom=?) AND (viewId=?) AND (cityToId=?) AND (cityFromId=?)";

	private static Logger logger = Logger.getLogger("log");

	private boolean isUsed(int id) {
		logger.info("Is this transport in use");
		try {// 1
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSPORT_IS_USED);
			int n;
			try {// 3
				statement.setLong(1, id);
				resultSet = statement.executeQuery();
				n = -2;
				resultSet.next();
				n = resultSet.getInt("rowcount");
			} finally {
			}// 3
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (n > 0) {
				logger.debug("This transport is in use");
				return true;
			} else {
				logger.debug("This transport is  NOT in use");
				return false;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		}
	}

	public boolean isExist(Transport transport) {
		logger.info("Is this transport exist");
		try {// 1
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSPORT_IS_EXIST);
			int n;
			try {// 3
				java.sql.Date date = new Date(transport.getDateTo()
						.getTimeInMillis());
				statement.setDate(1, date);
				date = new Date(transport.getDateFrom().getTimeInMillis());
				statement.setDate(2, date);
				statement.setLong(3, transport.getCityTo().getId());
				statement.setLong(4, transport.getCityFrom().getId());
				statement.setLong(5, transport.getView().getId());
				resultSet = statement.executeQuery();
				n = -2;
				resultSet.next();
				n = resultSet.getInt("rowcount");
			} finally {
			}// 3
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (n == 0 || n == -2) {
				logger.debug("This transport is NOT exist");
				return false;
			} else {
				logger.debug("This transport is already exist");
				return true;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		}

	}

	// C
	public Transport create(Transport transport) {
		logger.info("Create new transport");
		try {
			if (!isExist(transport)) {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_TRANSPORT_CREATE);
				java.sql.Date date = new Date(transport.getDateFrom()
						.getTimeInMillis());
				statement.setDate(1, date);
				date = new Date(transport.getDateTo().getTimeInMillis());
				statement.setDate(2, date);
				statement.setLong(3, transport.getView().getId());
				statement.setLong(4, transport.getCityFrom().getId());
				statement.setLong(5, transport.getCityTo().getId());
				statement.executeUpdate();

			}
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSPORT_CREATE_GET_ID);
			java.sql.Date date = new Date(transport.getDateTo()
					.getTimeInMillis());
			statement.setDate(1, date);
			date = new Date(transport.getDateFrom().getTimeInMillis());
			statement.setDate(2, date);
			statement.setLong(3, transport.getCityTo().getId());
			statement.setLong(4, transport.getCityFrom().getId());
			statement.setLong(5, transport.getView().getId());
			resultSet = statement.executeQuery();

			if (resultSet.next()) {
				transport.setId(resultSet.getInt("id"));
			}

			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}

		return transport;
	}

	// R
	public Transport readById(int id) {
		logger.info("Read transport by id");
		Transport transport = new Transport();
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSPORT_READ_BY_ID);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				transport.setId(resultSet.getInt("id"));

				GregorianCalendar dateTo = new GregorianCalendar();
				java.sql.Date date = resultSet.getDate("dateTo");
				dateTo.setTimeInMillis(date.getTime());
				transport.setDateTo(dateTo);

				GregorianCalendar dateFrom = new GregorianCalendar();
				date = resultSet.getDate("dateFrom");
				dateFrom.setTimeInMillis(date.getTime());
				transport.setDateFrom(dateFrom);

				TransportType tt = new TransportType();
				TransportTypeDAO td = new TransportTypeDAO();
				tt = td.readById(resultSet.getInt("viewId"));
				transport.setView(tt);

				City c = new City();
				CityDAO cd = new CityDAO();
				c = cd.readById(resultSet.getInt("cityToId"));
				transport.setCityTo(c);

				c = new City();
				cd = new CityDAO();
				c = cd.readById(resultSet.getInt("cityFromId"));
				transport.setCityFrom(c);
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return transport;
	}

	public Transport readByDatesViewAndCity(Transport transport) {
		logger.info("Read transport ByDatesViewAndCity");
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSPORT_READ_BY_ByDatesViewAndCity);
			java.sql.Date date = new Date(transport.getDateTo()
					.getTimeInMillis());
			statement.setDate(1, date);
			date = new Date(transport.getDateFrom().getTimeInMillis());
			statement.setDate(2, date);
			statement.setLong(3, transport.getView().getId());
			statement.setLong(4, transport.getCityTo().getId());
			statement.setLong(5, transport.getCityFrom().getId());

			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				transport.setId(resultSet.getInt("id"));
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return transport;
	}

	// U
	/* not necessary */
	public void updateDateTo(Transport transport) {
		logger.info("Update DateTo in transport");
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSPORT_UPDATE_DATETO);
			java.sql.Date date = new Date(transport.getDateTo()
					.getTimeInMillis());
			statement.setDate(1, date);
			statement.setInt(2, transport.getId());
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

	public void updateDateFrom(Transport transport) {
		logger.info("Update DateFrom in transport");
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSPORT_UPDATE_DATEFROM);
			java.sql.Date date = new Date(transport.getDateFrom()
					.getTimeInMillis());
			statement.setDate(1, date);
			statement.setInt(2, transport.getId());
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

	// D
	public void deleteById(int id) {
		logger.info("Delete this transport");
		if (!isUsed(id)) {
			try {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_TRANSPORT_DELETE);
				statement.setInt(1, id);
				statement.executeUpdate();
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				logger.error("SQL exeption");
			} catch (Exception e) {
				logger.error("Undefined exeption");
			} finally {
				logger.debug("Close connection");
				close();
			}
		}
	}
}
